package com.inspire.springboot.service;

import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.inspire.springboot.dbutil.DaoUtil;
import com.inspire.springboot.domain.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * @user Administrator
 * @className MySqlService
 * @data 2024年08月12日
 **/

@Service
public class MySqlService {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    private JdbcTemplate2 jdbcTemplate2=new JdbcTemplate2();

    public void query(){
        String sql = "select * from employee";

        List<Employee> query = jdbcTemplate.query(sql, (rs, rowNum) -> {
            Employee employee = new Employee();
            employee.setId(rs.getObject(1, Integer.class));
            employee.setPhoneNumber(rs.getObject(2, String.class));
            employee.setAccount(rs.getObject(3, String.class));
            employee.setPassword(rs.getObject(4, String.class));
            employee.setRealname(rs.getObject(5, String.class));
            employee.setCareer(rs.getObject(6, Integer.class));
            employee.setLevel(rs.getObject(7, String.class));
            employee.setEmail(rs.getObject(8, String.class));
            employee.setDeleted(rs.getObject(9, Boolean.class));
            employee.setArea(rs.getObject(10, String.class));
            employee.setCreateTime(rs.getObject(11, Date.class));
            employee.setModifiedTime(rs.getObject(12, Date.class));
            return employee;
        }, new TestInterface() {
            @Override
            public void run() {

            }
        },new TestInterface2(){
            @Override
            public void run(int i) {
                for (int j = 0; j <i; j++) {
                    System.out.println(i);
                }
            }
        });

        System.out.println(query);
    }

    public List<JSONObject> selectList() {

        return DaoUtil.query("1","2",()->{
            String sql = "SELECT a.*, e.realname, e.area FROM ( SELECT t.id as task_id, w.employee_id, w.work_time, w.work_desc, "
                    +"t.task_title, t.project_name, t.task_priority, DATE_FORMAT(t.task_create_time,'%Y-%m-%d') as start_date, "
                    +"DATE_FORMAT(t.task_etic,'%Y-%m-%d') as end_time, s.use_time FROM taskinfo t inner JOIN work_info w ON w.task_id = "
                    +"t.id LEFT JOIN ( SELECT employee_id,task_id,sum(work_time) as use_time FROM work_info WHERE week_day <= '2024-06-17' GROUP BY task_id,employee_id ) "
                    +"as s ON w.employee_id = s.employee_id and w.task_id = s.task_id WHERE w.week_day = '2024-06-17' ) AS a LEFT JOIN employee e ON a.employee_id = e.id";
            return jdbcTemplate2
                    .query(
                            () -> new UfBaseException("1").setParam("zoa", "test")
                                    .setParam("1", "2")
                                    .setParam("2", "3"),
                            sql,
                            new PreparedStatementSetter() {
                                @Override
                                public void setValues(PreparedStatement ps) throws SQLException {
                                    ps.setObject(1, "test");
                                    ps.setObject(2, "2");
                                    ps.setObject(3, "3");
                                }
                            },
                            new RowMapper<JSONObject>() {
                                @Override
                                public JSONObject mapRow(ResultSet rs, int rowNum) throws SQLException {
                                    JSONObject jsonObject = new JSONObject();
                                    ResultSetMetaData metaData = rs.getMetaData();
                                    int columnCount = metaData.getColumnCount();
                                    for (int i = 1; i <= columnCount; i++) {
                                        Object object = rs.getObject(i);
                                        String columnName = metaData.getColumnName(i);
                                        jsonObject.put(columnName, object);
                                    }
                                    return jsonObject;
                                }
                            });
        },new TypeReference<List<JSONObject>>(){},"2","3");
    }
}
